/**
 * 
 */
package dstation.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import dstation.databeans.SensorBean;
import dstation.databeans.SensorBean.SensorType;

/**
 * @author Evan
 *
 */
public class SensorDAO {
	private static String insertString;
	private static String lookupString;
	private static String updateString;
	private static String retrieveString;

	/**
	 * Instantiate a new Sensor data access object
	 * @param tableName The name of the Sensor table
	 */
	public SensorDAO(String tableName){
		insertString = "insert into "+tableName+" (name, type) values ( ?, ?)";
		lookupString = "select * from "+tableName+" where sensorid = ?";
		retrieveString = "select * from "+tableName;
		updateString = "update "+tableName+" set name = ? , type = ? where sensorid = ?";
		try {
			Connection con = DriverManager.getConnection("jdbc:mysql:///colony");
			ResultSet tableNames = con.prepareStatement("SHOW TABLES").executeQuery();
			boolean exists = false;
			while(tableNames.next()){
				String temp = tableNames.getString(1);
				if(temp.equals(tableName)){
					exists = true;
					break;
				}
			}
			if(!exists){
				Statement stmt = con.createStatement();
				stmt.executeUpdate("create table "+tableName+" (sensorid int not null auto_increment," +
						" name varchar(100) not null, type enum('BOM','ENCODER','RF') not null," +
				" primary key(sensorid), unique (name))");
				stmt.close();
			}
			tableNames.close();
			con.close();
			if(!exists){
				populateInitial();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void populateInitial() throws SQLException {
		String[] names = {"Bom","Left Encoder","Right Encoder","Rangefinder 1",
				"Rangefinder 2","Rangefinder 3","Rangefinder 4","Rangefinder 5"};
		SensorType[] types = {SensorType.BOM,SensorType.ENCODER,SensorType.ENCODER,
				SensorType.RF,SensorType.RF,SensorType.RF,SensorType.RF,SensorType.RF};
		for(int i = 0; i < names.length; i++){
			SensorBean s = new SensorBean();
			s.setName(names[i]);
			s.setType(types[i]);
			insert(s);
		}
	}

	/**
	 * Add a new sensor to the database
	 * @param s The sensor to insert (id is ignored)
	 * @return The newly inserted sensor
	 * @throws SQLException 
	 */
	public SensorBean insert(SensorBean s) throws SQLException{
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			stmt = con.prepareStatement(insertString, Statement.RETURN_GENERATED_KEYS);
			stmt.setString(1, s.getName());
			stmt.setString(2, s.getType().toString());
			stmt.executeUpdate();
			con.commit();
			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			s.setId(rs.getInt(1));
			stmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(stmt!=null){stmt.close();}
			if(con!=null){con.close();}
		}
		return s;
	}

	/**
	 * Retrieve a sensor from the table
	 * @param id The sensor id
	 * @return The retrieved sensor
	 * @throws SQLException 
	 */
	public SensorBean lookup(int id) throws SQLException{
		Connection con = null;
		PreparedStatement stmt = null;
		SensorBean s = new SensorBean();
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			stmt = con.prepareStatement(lookupString);
			stmt.setInt(1, id);
			ResultSet rs = stmt.executeQuery();
			con.commit();
			s.setId(rs.getInt("sensorid"));
			s.setName(rs.getString("name"));
			s.setType(SensorBean.SensorType.valueOf(rs.getString("type")));
			stmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(stmt!=null){stmt.close();}
			if(con!=null){con.close();}
		}
		return s;
	}

	/**
	 * Update data about a sensor in the table
	 * @param s A bean containing the sensor/info to update
	 */
	public void update(SensorBean s){
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			stmt = con.prepareStatement(updateString);
			stmt.setString(1, s.getName());
			stmt.setString(2, s.getType().toString());
			stmt.setInt(3, s.getId());
			stmt.executeUpdate();
			con.commit();
			stmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try{
				if(stmt!=null){stmt.close();}
				if(con!=null){con.close();}
			} catch (SQLException e) {
			}
		}
	}

	public List<SensorBean> retrieve(int status) throws SQLException {
		Connection con = null;
		PreparedStatement stmt = null;
		List<SensorBean> list = new ArrayList<SensorBean>();
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			stmt = con.prepareStatement(retrieveString);
			ResultSet rs = stmt.executeQuery();
			con.commit();
			while(rs.next()){
				SensorBean s = new SensorBean();
				s.setId(rs.getInt("sensorid"));
				s.setName(rs.getString("name"));
				s.setType(SensorBean.SensorType.valueOf(rs.getString("type")));
				if((Math.round(Math.pow(2,s.getId()))&status) == 0){
					list.add(s);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(stmt!=null){stmt.close();}
			if(con!=null){con.close();}

		}
		return list;
	}
}
